﻿package 
{
	import flash.events.SQLEvent;
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.filesystem.File;
	import flash.events.MouseEvent;
	import flash.data.SQLResult;
	import flash.events.EventDispatcher;
	import flash.events.Event;
	import flash.errors.SQLError;
	
	public class Util extends EventDispatcher
	{
		private var conn:SQLConnection;
		public var res:Array = new Array();
		private var tenten:Array = new Array();
		private var dagen:Array = new Array();
		private var xmlArray:Array = new Array();
		private var indexDagen:Array = new Array();
		private var indexStages:Array = new Array();
		private var artiest:Array = new Array();
		private var dag:int;
		private var tent:Number;
		private var update:String;
		private var saveUpdate:String;
		
		
		public function Util()
		{			
			//leeg
		}
		
		public function startINIT(){
			//create a new sql connection
			conn = new SQLConnection();
			//add an event handeler for the open event
			conn.addEventListener(SQLEvent.OPEN, createTables);
			//create the database if it doesn't exist, otherwise just opens it
			var dbFile:File=File.applicationDirectory.resolvePath("dranoidTest.db");
			conn.openAsync(dbFile);
		}
		
		public function closeConn(){
			conn.addEventListener(SQLEvent.CLOSE, onCloseConn);
			conn.close();
		}
		
		public function onCloseConn(e:SQLEvent):void{
			dispatchEvent(new Event("CLOSED"));
		}

		private function createTables(event:SQLEvent):void {
			//create a new sql statement
			var sql:SQLStatement = new SQLStatement();
			//set the statement to connect to our database
			sql.sqlConnection=conn;
			//parse the sql command that creates the table if it doesn't exist
			sql.text= "CREATE TABLE IF NOT EXISTS Artiest (id INTEGER PRIMARY KEY AUTOINCREMENT, IDArtiest TEXT, Naam TEXT, Info TEXT, Foto BLOB, Website TEXT, Youtube TEXT, Myspace TEXT)";
			//call the execute function to execute our statement
			sql.execute();
					
			//Het zelfde voor de andere tabellen
			var sql4:SQLStatement = new SQLStatement();
			sql4.sqlConnection=conn;
			sql4.text = "CREATE TABLE IF NOT EXISTS Festivaldag (id INTEGER PRIMARY KEY AUTOINCREMENT, Naam TEXT, Opendeur TEXT, Datum TEXT)";
			sql4.execute();
			
			//Het zelfde voor de andere tabellen
			var sql5:SQLStatement = new SQLStatement();
			sql5.sqlConnection=conn;
			sql5.text = "CREATE TABLE IF NOT EXISTS Optreden (id INTEGER PRIMARY KEY AUTOINCREMENT, Startuur TEXT, Einduur TEXT, Artiestid INTEGER, Festivaldagid INTEGER, Podiumid INTEGER)";
			sql5.execute();
			
			//Het zelfde voor de andere tabellen
			var sql6:SQLStatement = new SQLStatement();
			sql6.sqlConnection=conn;
			sql6.text = "CREATE TABLE IF NOT EXISTS Podium (id INTEGER PRIMARY KEY AUTOINCREMENT, Naam TEXT)";
			sql6.execute();
			
			//Het zelfde voor de andere tabellen
			var sql7:SQLStatement = new SQLStatement();
			sql7.sqlConnection=conn;
			sql7.text = "CREATE TABLE IF NOT EXISTS UpdateApp (id INTEGER PRIMARY KEY AUTOINCREMENT, lastUpdate TEXT)";
			sql7.execute();
			
			//Het zelfde voor de andere tabellen
			var sql8:SQLStatement = new SQLStatement();
			sql8.sqlConnection=conn;
			sql8.text = "INSERT INTO UpdateApp (lastUpdate) VALUES (:lastUpdate)";
			
			sql8.parameters[":lastUpdate"] = "null";
					
			sql8.addEventListener(SQLEvent.RESULT, disInit);
			sql8.execute();
			sql8.clearParameters();		
					
		}
		
		public function disInit(e:SQLEvent):void{
			dispatchEvent(new Event("INIT"));
		}
		
		public function doLocalUpdate():void{
			//create a new sql statemant
			var sql9:SQLStatement = new SQLStatement();
			sql9.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			sql9.text =  "SELECT lastUpdate FROM UpdateApp";
			//sql.text = "SELECT Naam FROM Artiest";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql9.addEventListener(SQLEvent.RESULT, getLastUpdate);
			sql9.execute();
		}
		
		public function getLastUpdate(e:SQLEvent):void{
			update = e.target.getResult().data[0].lastUpdate;
			trace("De upate from db" + update);
			dispatchEvent(new Event("LOCAL"));
		}
		
		public function getLocalUpdate():String{
			return update;
		}
		
		public function setUpdate(xmlUpdate:String):void{
			saveUpdate = xmlUpdate;
			//create a new sql statemant
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			//verwijderen van alle data in de table UpdateApp
			sql.text = "DELETE FROM UpdateApp";
			//sql.text = "SELECT Naam FROM Artiest";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			sql.addEventListener(SQLEvent.RESULT, setDateTime);
			sql.execute();
		}
		
		public function setDateTime(e:SQLEvent):void{
			
			var d:Date = new Date();
			//create a new sql statemant
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			//verwijderen van alle data in de table UpdateApp
			sql.text = "INSERT INTO UpdateApp (lastUpdate) VALUES (:lastUpdate)";
			
			sql.parameters[":lastUpdate"] = saveUpdate;
			
			sql.addEventListener(SQLEvent.RESULT, sendEventSaveUpdate);
			sql.execute();
			sql.clearParameters();
		}
		
		public function sendEventSaveUpdate(e:SQLEvent):void{
			dispatchEvent(new Event("SAVED"));
		}
	
		public function retrieveData():void {
			//create a new sql statemant
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			sql.text =  "SELECT a.Naam AS Naam, o.Startuur AS Startuur, o.Einduur AS Einduur, f.Naam AS Dag, p.Naam AS Podium FROM Optreden o, Artiest a, Festivaldag f, Podium p WHERE o.Artiestid = a.IDArtiest and o.Festivaldagid = f.id and o.Podiumid = p.id ORDER BY a.naam";
			//sql.text = "SELECT Naam FROM Artiest";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql.addEventListener(SQLEvent.RESULT, selectHandler);
			sql.execute();
			
		}
		
		public function retrieveShedule(dag:Number, tent:Number):void {
			//create a new sql statemant
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			sql.text =  "SELECT a.Naam AS Naam, o.Startuur AS Startuur, o.Einduur AS Einduur, f.Naam AS Dag, p.Naam AS Podium FROM Optreden o, Artiest a, Festivaldag f, Podium p WHERE o.Artiestid = a.IDArtiest and o.Festivaldagid = f.id and o.Festivaldagid = '" + dag + "' and o.Podiumid = p.id and o.Podiumid = '" + tent + "' ORDER BY a.naam";
			//sql.text = "SELECT Naam FROM Artiest";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql.addEventListener(SQLEvent.RESULT, selectHandler);
			sql.execute();
			
		}
		
		public function zoeken(s:String):void {
			//create a new sql statemant
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			sql.text =  "SELECT a.Naam AS Naam, o.Startuur AS Startuur, o.Einduur AS Einduur, f.Naam AS Dag, p.Naam AS Podium FROM Optreden o, Artiest a, Festivaldag f, Podium p WHERE o.Artiestid = a.IDArtiest and o.Festivaldagid = f.id and o.Podiumid = p.id and a.Naam LIKE '%" + s + "%' ORDER BY a.naam";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql.addEventListener(SQLEvent.RESULT, selectHandler);
			sql.execute();
			
		}
		
		public function getAllTenten():void{
			//create a new sql statemant
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			//sql.text =  "SELECT a.Naam AS Naam, o.Startuur AS Startuur, o.Einduur AS Einduur, f.Naam AS Dag, p.Naam AS Podium FROM Optreden o, Artiest a, Festivaldag f, Podium p WHERE o.Artiestid = a.id and o.Festivaldagid = f.id and o.Podiumid = p.id ORDER BY a.naam";
			sql.text = "SELECT id, Naam FROM Podium";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql.addEventListener(SQLEvent.RESULT, getAllDagen);
			sql.execute();
		}
		
		public function getAllDagen(e:SQLEvent):void{
			
			tenten = e.target.getResult().data;
			
			//create a new sql statemant
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			//sql.text =  "SELECT a.Naam AS Naam, o.Startuur AS Startuur, o.Einduur AS Einduur, f.Naam AS Dag, p.Naam AS Podium FROM Optreden o, Artiest a, Festivaldag f, Podium p WHERE o.Artiestid = a.id and o.Festivaldagid = f.id and o.Podiumid = p.id ORDER BY a.naam";
			sql.text = "SELECT id, Datum FROM Festivaldag";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql.addEventListener(SQLEvent.RESULT, fillOptreden);
			sql.execute();
		}
		
		//Inlezen van array en dan eerste alle tabellen leeg maken (behalve update tabel)
		public function updateFromXML(a:Array):void{
			xmlArray = a;
			
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			sql.text = "DELETE FROM Optreden";
			sql.addEventListener(SQLEvent.RESULT, deleteArtiest);
			sql.execute();			
			
		}
		
		public function deleteArtiest(e:SQLEvent):void{
					
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			sql.text = "DELETE FROM Artiest";
			sql.addEventListener(SQLEvent.RESULT, deletedag);
			sql.execute();			
			
		}
		
		public function deletedag(e:SQLEvent):void{
					
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			sql.text = "DELETE FROM Festivaldag";
			sql.addEventListener(SQLEvent.RESULT, deletePodium);
			sql.execute();			
			
		}
		
		public function deletePodium(e:SQLEvent):void{
					
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			sql.text = "DELETE FROM Podium";
			sql.addEventListener(SQLEvent.RESULT, saveFromXML);
			sql.execute();			
			
		}
				
		public function saveFromXML(e:SQLEvent):void{
			
			//Opvullen van Artiest tabel
			for (var i:Number=0; i < xmlArray.length; i++){
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
										
			sql.text = "INSERT INTO Artiest (IDArtiest, Naam, Info, Foto, Website, Youtube, Myspace) VALUES (:id, :Naam, :Info, :Foto, :Website, :Youtube, :Myspace)";
			
			
			sql.parameters[":id"] = xmlArray[i].getID().toString();
			sql.parameters[":Naam"] = xmlArray[i].getTitle();
			sql.parameters[":Info"] = xmlArray[i].getdescription();
			sql.parameters[":Foto"] = xmlArray[i].getFoto();
			sql.parameters[":Website"] = xmlArray[i].getwebsite();
			sql.parameters[":Youtube"] = xmlArray[i].getyoutube();
			sql.parameters[":Myspace"] = xmlArray[i].getMyspace();
			sql.execute();
			sql.clearParameters();
			
			}
			
			//De unieke festivaldagen uit het array halen
			var UniekDag:Array = new Array();
			for(var j:Number=0; j < xmlArray.length; j++){
				if ( !checkArray(UniekDag, xmlArray[j].getstartdate())){
					UniekDag.push(xmlArray[j].getstartdate());
				}
			}
			
			//De unieke tenten uit het array halen
			var Uniektent:Array = new Array();
			for(var jj:Number=0; jj < xmlArray.length; jj++){
				
				if ( xmlArray[jj].gettent() != " " ){
					if ( !checkArray(Uniektent, xmlArray[jj].gettent())){
						Uniektent.push(xmlArray[jj].gettent());
					}
				}
			}
			
			trace(Uniektent + "de tenten");
				
			//opvullen van festivaldag tabel, alle unieke dagen worden toegevoegd
			for (var ii:Number=0; ii < UniekDag.length; ii++){
			//omzetten van yyyy-mm-dd naar een dag van de week
			var weekDayLabels:Array = new Array("ZONDAG", "MAANDAG", "DINSDAG", "WOENSDAG", "DONDERDAG", "VRIJDAG", "ZATERDAG");
					
			var d:Date = new Date(Date.parse(UniekDag[ii].split("-").join("/")));
			
			var sql2:SQLStatement = new SQLStatement();
			sql2.sqlConnection=conn;
										
			sql2.text = "INSERT INTO Festivaldag (Naam, Datum) VALUES (:Naam, :Datum)";
			
			sql2.parameters[":Naam"] = weekDayLabels[d.getDay()];
			sql2.parameters[":Datum"] = UniekDag[ii];
			sql2.execute();
			sql2.clearParameters();
			
			}	
			
			//opvullen van podium tabel, alle unieke dagen worden toegevoegd
			for (var iii:Number=0; iii < UniekDag.length-1; iii++){
			//omzetten van yyyy-mm-dd naar een dag van de week
					
			var sql3:SQLStatement = new SQLStatement();
			sql3.sqlConnection=conn;
										
			sql3.text = "INSERT INTO Podium (Naam) VALUES (:Naam)";
			
			sql3.parameters[":Naam"] = Uniektent[iii];
			sql3.execute();
			sql3.clearParameters();
			
			}
			
			//Ophalen van tentid's, daarna de dagid's en hierna worden dan de optreden gegevens opgeslaan
			getAllTenten();
			
			
		}
		
		private function fillOptreden(e:SQLEvent):void{
			trace("fillOptreden");
			
			dagen = e.target.getResult().data;
			
			//opvullen van podium tabel, alle unieke dagen worden toegevoegd
			for (var iiii:Number=0; iiii < xmlArray.length; iiii++){
			//omzetten van yyyy-mm-dd naar een dag van de week
			var tentid:Number = -1;		
			var dagid:Number = -1;
								
			for (var t:Number=0; t < tenten.length; t++){
				if (tenten[t].Naam == xmlArray[iiii].gettent()){
					tentid = tenten[t].id;
					//trace("Artiest" + xmlArray[iiii].getID() + "Tentid:" + tentid);
				}
			
			}
			for (var d:Number=0; d < dagen.length; d++){
							if (dagen[d].Datum == xmlArray[iiii].getstartdate()){
								dagid = dagen[d].id;
								//trace("Artiest" + xmlArray[iiii].getID() + "dagid:" + dagid);
							} //for
			} //for
			
			if (dagid != -1 || tentid != -1){
			var sql4:SQLStatement = new SQLStatement();
			sql4.sqlConnection=conn;
															
			sql4.text = "INSERT INTO Optreden (Startuur, Einduur, Artiestid , Festivaldagid , Podiumid) VALUES (:Startuur, :Einduur, :Artiestid , :Festivaldagid , :Podiumid)";
								
			sql4.parameters[":Startuur"] = xmlArray[iiii].getstarttime();
			sql4.parameters[":Einduur"] = xmlArray[iiii].getendtime();
			sql4.parameters[":Artiestid"] = xmlArray[iiii].getID();
			sql4.parameters[":Festivaldagid"] = dagid;
			sql4.parameters[":Podiumid"] = tentid;
			sql4.execute();
			sql4.clearParameters();
			}
			}
			
			
			dispatchEvent(new Event("UP"));
			
			}
			
			
		public function checkArray(a:Array, s:String){
			for (var i:Number =0; i<a.length; i++) {
				if (a[i] == s) {
				return true;
				}
				}
				return false;
		}

		public function selectHandler(event:SQLEvent):void {
			
			res = event.target.getResult().data;
			trace("de data: " + res);
			dispatchEvent(new Event("COMPLETED"));
		}	
					
		public function getData():Array
		{
			return res;
		}
		
		public function retrieveDays():void{
						
			//create a new sql statemant
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			//sql.text =  "SELECT a.Naam AS Naam, o.Startuur AS Startuur, o.Einduur AS Einduur, f.Naam AS Dag, p.Naam AS Podium FROM Optreden o, Artiest a, Festivaldag f, Podium p WHERE o.Artiestid = a.id and o.Festivaldagid = f.id and o.Podiumid = p.id ORDER BY a.naam";
			sql.text = "SELECT id, Naam FROM Festivaldag ORDER BY naam";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql.addEventListener(SQLEvent.RESULT, setDays);
			sql.execute();
		}
		
		public function retrieveArtiest(s:String):void{
						
			//create a new sql statemant
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			
			sql.text = "SELECT a.Naam AS Naam, a.Foto AS Foto, a.Info AS Info, a.Website AS Web, a.Youtube AS You, a.MySpace AS Space, o.Startuur AS Startuur, o.Einduur AS Einduur, f.Naam AS Dag, p.Naam AS Podium FROM Optreden o, Artiest a, Festivaldag f, Podium p WHERE o.Artiestid = a.IDArtiest and o.Festivaldagid = f.id and o.Podiumid = p.id and a.Naam = '" + s + "'";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql.addEventListener(SQLEvent.RESULT, giveArtiest);
			sql.execute();
		}
		
		public function retrieveStages():void{
			//create a new sql statemant
			var sql:SQLStatement = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			//sql.text =  "SELECT a.Naam AS Naam, o.Startuur AS Startuur, o.Einduur AS Einduur, f.Naam AS Dag, p.Naam AS Podium FROM Optreden o, Artiest a, Festivaldag f, Podium p WHERE o.Artiestid = a.id and o.Festivaldagid = f.id and o.Podiumid = p.id ORDER BY a.naam";
			sql.text = "SELECT id, Naam FROM Podium ORDER BY id";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql.addEventListener(SQLEvent.RESULT, setStages);
			sql.execute();
		}
		
		public function giveArtiest(e:SQLEvent){
			artiest = e.target.getResult().data;
			dispatchEvent(new Event("ARTIEST"));
		}
		
		public function getArtiest():Array{
			return artiest;
		}
		
		public function setDays(e:SQLEvent){
			indexDagen = e.target.getResult().data;
			trace(indexDagen);
			dispatchEvent(new Event("DAYS"));
		}
		
		public function setStages(e:SQLEvent){
			indexStages = e.target.getResult().data;
			trace(indexStages);
			dispatchEvent(new Event("STAGES"));
		}		
		
		public function getDays():Array{
			return indexDagen;
		}
		
		public function getStages():Array{
			return indexStages;
		}
		
		


	}

}